package Server;

import gui.ExitFrame;


import java.io.IOException;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.Format;
import java.text.SimpleDateFormat;
import java.util.Date;

import javax.swing.JFrame;

import sun.nio.cs.Surrogate.Generator;

import client.Client;



import com.mysql.jdbc.Connection;
import com.mysql.jdbc.PreparedStatement;

public class MyReport 
{
	static String g;
	Object o;
	static Object tmp_obj;;
	public static Client client1;
	public static String[] Userinfo;
	static String ansStr[];
	static String stri;
	static String stri2;
	static int j=0;
	int k,y, wo;
	static int r=0;
	
	
	static Object[][] obj, res_obj;
	private static String strin1;
	private static String strin2;
	private static int int1;
	private static String strin3;
	private static long sumi;

	/**
	 * 
	 * @param con2 - 
	 * @param str - information to send to the table at the qui
	 * @return
	 */
	public static Object[][] func(Connection con2, String[] str) 
	{		
		System.out.println(str[0]);
		System.out.println(str[1]);
		System.out.println(str[2]);
		System.out.println(str[3]);
		System.out.println(str[4]);
		System.out.println(str[5]);
		
		
		Statement stmt1;
		String sql;
		ResultSet rs;
		int cnt = 0;
		
		String from, to;
		long l_from,l_to, l_num;
		
		try 
		{		
			/**
			 * get data from items table
			 */
			if(str[5].toCharArray()[0]=='1')
			{
				sql="SELECT name,quantity FROM items";
				System.out.println("sql");
				stmt1 = con2.createStatement();	
				rs=stmt1.executeQuery(sql);
			
				int rowcount = 0;
				if (rs.last()) 
				{
					rowcount = rs.getRow();
					rs.beforeFirst(); // not rs.first() because the rs.next() below will move on, missing the first element
				}
				int numbColumns = rs.getMetaData().getColumnCount();
				obj=new Object[rowcount][numbColumns];			
			
				for(r=0;rs.next();r++)
				{
					int numColumns = rs.getMetaData().getColumnCount();
					for ( int i=1 ; i <= numColumns ; i++ ) 
					{
						obj[r][i-1]=rs.getObject(i);      
					}
				}
			}
			
			
			
			/**
			 * get data from orders table and check if it in the limits
			 */
			if(str[2].toCharArray()[0]=='1')
			{
				
					from=str[6];
					to=str[7];			
					Object obj2;
					
					String e = "";
					
					for (int i = 0; i < from.length(); i ++) {
					      if (from.charAt(i) != '/') e += from.charAt(i);
					   }
			
					l_from= Long.parseLong(e.trim());

					
					String f = "";
					
					for (int i = 0; i < to.length(); i ++) {
					      if (to.charAt(i) != '/') f += to.charAt(i);
					   }
			
					l_to= Long.parseLong(f.trim());
					
					
					
					System.out.println(l_from);
					System.out.println(l_to);
					
					
					
					sql="SELECT order_num,order_time FROM orders";
					System.out.println("sql");
					stmt1 = con2.createStatement();	
					rs=stmt1.executeQuery(sql);
				
					
					int rowcount = 0;
					if (rs.last()) 
					{
						rowcount = rs.getRow();
						rs.beforeFirst(); // not rs.first() because the rs.next() below will move on, missing the first element
					}
					int numbColumns = rs.getMetaData().getColumnCount();
					obj=new Object[rowcount][numbColumns];			
					res_obj=new Object[rowcount][numbColumns];			
					
					
					
					for(r=0;rs.next();r++)
					{
						int numColumns = rs.getMetaData().getColumnCount();
						for ( int i=1 ; i <= numColumns ; i++ ) 
						{
							obj[r][i-1]=rs.getObject(i);
							
						}
						strin1=rs.getObject(1).toString();
						strin2=rs.getObject(2).toString();
						
						
						
						stri=obj[r][1].toString().substring(1, 10);
													
						g = "";						
						for (int b = 0; b < stri.length(); b ++) {
						      if (stri.charAt(b) != '/') g += stri.charAt(b);
						   }
						l_num= Long.parseLong(g.trim());						
						System.out.println(l_num);
						if((l_from<l_num)&&(l_to>l_num))
						{
						
							res_obj[cnt][0]=(Object)strin1;
							res_obj[cnt][1]=(Object)strin2;
//							
							cnt++;
						
						}
						
					}
					
					return res_obj;
			
			}

			
			
			/**
			 * get data from complaint table, check if it between the limits
			 */
			
			if(str[3].toCharArray()[0]=='1')
			{
				
					from=str[6];
					to=str[7];			
					Object obj2;
					
					String e = "";
					
					for (int i = 0; i < from.length(); i ++) {
					      if (from.charAt(i) != '/') e += from.charAt(i);
					   }
			
					l_from= Long.parseLong(e.trim());

					
					String f = "";
					
					for (int i = 0; i < to.length(); i ++) {
					      if (to.charAt(i) != '/') f += to.charAt(i);
					   }
			
					l_to= Long.parseLong(f.trim());
					
					
					
					System.out.println(l_from);
					System.out.println(l_to);
					
					
					
					sql="SELECT idc,DATE FROM complaint";
					System.out.println("sql");
					stmt1 = con2.createStatement();	
					rs=stmt1.executeQuery(sql);
				
					
					int rowcount = 0;
					if (rs.last()) 
					{
						rowcount = rs.getRow();
						rs.beforeFirst(); 
					}
					int numbColumns = rs.getMetaData().getColumnCount();
					obj=new Object[rowcount][numbColumns];			
					res_obj=new Object[rowcount][numbColumns];			
					
					
					
					for(r=0;rs.next();r++)
					{
						int numColumns = rs.getMetaData().getColumnCount();
						for ( int i=1 ; i <= numColumns ; i++ ) 
						{
							obj[r][i-1]=rs.getObject(i);
							
						}
						strin1=rs.getObject(1).toString();
						strin2=rs.getObject(2).toString();
						
						
						stri=(obj[r][1].toString()).substring(1, 10);
													
						g = "";						
						for (int b = 0; b < stri.length(); b ++) {
						      if (stri.charAt(b) != '/') g += stri.charAt(b);
						   }
						l_num= Long.parseLong(g.trim());						
						System.out.println(l_num);
						if((l_from<l_num)&&(l_to>l_num))
						{
						
							res_obj[cnt][0]=(Object)strin1;
							res_obj[cnt][1]=(Object)strin2;
//							
							cnt++;
						
						}
						
					}
					
					return res_obj;
			
			}

			
			/**
			 * get date from survey and questionnare table and make average
			 */
			
			if(str[4].toCharArray()[0]=='1')
			{
				int rowcount = 0;
				int rowcount2 = 0;
				int[] iRet = new int[2];
				Object[][] obj = null;
				
				
				sql="SELECT * FROM survey";


					stmt1 = con2.createStatement();				
					rs = stmt1.executeQuery(sql);	
					if (rs.last()) 
					{
						rowcount = rs.getRow();
						rs.beforeFirst(); // not rs.first() because the rs.next() below will move on, missing the first element
					}
					
		
					System.out.print(rowcount);
					obj = new Object[rowcount][2];
				int num_survey= rowcount;
				int m,n,p, sum=0, cnt1=0;
				String strn=new String();
				double avg;
				for(m=1, cnt=0;m<=num_survey;m++)
				{
					
					sum=0;
					cnt1=0;
					sql="SELECT * FROM questionnare WHERE survey_num="+m;
					stmt1 = con2.createStatement();	
					rs = stmt1.executeQuery(sql);
					while(rs.next())
					{
					strn=rs.getObject(3).toString();
					sum+=Integer.parseInt(strn);
					strn=rs.getObject(4).toString();
					sum+=Integer.parseInt(strn);
					strn=rs.getObject(5).toString();
					sum+=Integer.parseInt(strn);
					strn=rs.getObject(6).toString();
					sum+=Integer.parseInt(strn);
					strn=rs.getObject(7).toString();
					sum+=Integer.parseInt(strn);
					strn=rs.getObject(8).toString();
					sum+=Integer.parseInt(strn);
					System.out.print(sum);
					cnt1+=6;
					//sum = Integer.parseInt(rs.getObject(1).toString());
					//	+Integer.parseInt((String) rs.getObject(2))+
					//	Integer.parseInt((String) rs.getObject(3));
					}
					avg=(double)sum/cnt1;
					
				//	avg=generator.nextInt(6);
					strin1 = Integer.toString(m);
					strin2 = Double.toString(avg);
					obj[m-1][0]=(Object)strin1;
					obj[m-1][1]=(Object)strin2;
				}
					
					return obj;
			}
				

			
			/**
			 * get date from orders table and check if it between today and before 3 months
			 */
			
			if(str[1].toCharArray()[0]=='1')
			{
				String st;
				sumi=0;
				Date date = new Date();
				Format form;	
				form = new SimpleDateFormat("yyyy/MM/dd");
				String s = form.format(date);
				System.out.print(s);
				st=s.substring(5,7);
				int a = Integer.parseInt(st);
				a=(a-3)%12;
				String sst = Integer.toString(a);
				String st1=s.substring(0,5);
				String st2=s.substring(7,10);
				String stst = st1+"0"+sst+st2;
				
				
					from=stst;
					to=s;			
					Object obj2;
					
					String e = "";
					
					for (int i = 0; i < from.length(); i ++) {
					      if (from.charAt(i) != '/') e += from.charAt(i);
					   }
			
					l_from= Long.parseLong(e.trim());

					
					String f = "";
					
					for (int i = 0; i < to.length(); i ++) {
					      if (to.charAt(i) != '/') f += to.charAt(i);
					   }
			
					l_to= Long.parseLong(f.trim());
					
					
					
					System.out.println(l_from);
					System.out.println(l_to);
					
					
					
					sql="SELECT order_num,order_time,sum FROM orders";
					System.out.println("sql");
					stmt1 = con2.createStatement();	
					rs=stmt1.executeQuery(sql);
				
					
					int rowcount = 0;
					if (rs.last()) 
					{
						rowcount = rs.getRow();
						rs.beforeFirst(); // not rs.first() because the rs.next() below will move on, missing the first element
					}
					int numbColumns = rs.getMetaData().getColumnCount();
					obj=new Object[rowcount][numbColumns];			
					res_obj=new Object[rowcount+1][numbColumns];			
					
					
					
					for(r=0;rs.next();r++)
					{
			
						int numColumns = rs.getMetaData().getColumnCount();
						for ( int i=1 ; i <= numColumns ; i++ ) 
						{
							obj[r][i-1]=rs.getObject(i);
							
						}
						strin1=rs.getObject(1).toString();
						strin2=rs.getObject(2).toString();
						strin3=rs.getObject(3).toString();
						
						stri=(obj[r][1].toString()).substring(0, 10);
													
						g = "";						
						for (int b = 0; b < stri.length(); b ++) {
						      if (stri.charAt(b) != '/') g += stri.charAt(b);
						   }
						l_num= Long.parseLong(g.trim());						
						System.out.println(l_num);
						
						if((l_from<l_num)&&(l_to>l_num))
						{
						
							res_obj[cnt][0]=(Object)strin1;
							res_obj[cnt][1]=(Object)strin2;
							res_obj[cnt][2]=(Object)strin3;
							cnt++;
							sumi+=Long.parseLong(strin3);
						}
						
					}
					String sss = Long.toString(sumi);
					String sm="sum";
					res_obj[cnt][0]=(Object)sm;
					res_obj[cnt][1]=(Object)"-";
					res_obj[cnt][2]=(Object)sumi;
					return res_obj;
			
			}


			
			
			
		}

			
			
			
		 
		catch (SQLException e) 
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		}	
	
		
		
		return obj;
		
	}	

	
	
}
